import pandas as pd
from es_pandas import es_pandas

from air_web.config.config import config, dws_agg_conf
from air_web.data_platform import sql_engine


class AddVirtualUsers:
    def __init__(self, table_name):
        self.ep = es_pandas(config["ES_HOST"])
        self.table_name = table_name
        self.res_df = pd.DataFrame()
        self.select_sql = """sum(contract_cap) as contract_cap, 
                             sum(run_cap) as run_cap, 
                             '0' as lc_flag,
                             1 as mp_level,
                             max(abso_mode) as abso_mode,
                             999999999 as org_no
                          """

    def all_province(self, province_no, province_region):
        sql = """select 
                    concat('99999', '_', {province_no}) as id, 
                    concat('99999', '_', {province_no}) as cons_no, 
                    concat('全行业', '_', '全省') as cons_name,
                    {province_region} as region_code,
                    99999 as type_id,
                    '全行业' as type_code,
                    99999 as pare_type_id,
                    '全行业' as pare_type_code,
                    99999 as type_code_sort,
                    99999 as on5,
                    9999999 as on7,
                    '全省' as shi,
                    '全省' as xian,
                    '全省' as org_name,
                    {select_sql}
                 from {table_name}
              """.format(
            table_name=table_name,
            select_sql=self.select_sql,
            province_no=province_no,
            province_region=province_region,
        )
        df = sql_engine.query(sql)
        self.res_df = pd.concat([self.res_df, df])

    def pare_province(self, province_no, province_region):
        sql = """select 
                   concat(pare_type_id, '_', {province_no}) as id, 
                   concat(pare_type_id, '_', {province_no}) as cons_no, 
                   concat(pare_type_code, '_', '全省') as cons_name,
                   {province_region} as region_code,
                   pare_type_id as type_id,
                   pare_type_code as type_code,
                   pare_type_id,
                   pare_type_code,
                   type_code_sort,
                   99999 as on5,
                   9999999 as on7,
                   '全省' as shi,
                   '全省' as xian,
                   '全省' as org_name,
                   {select_sql}
                from {table_name} 
                group by pare_type_id, pare_type_code, type_code_sort
             """.format(
            table_name=table_name,
            select_sql=self.select_sql,
            province_no=province_no,
            province_region=province_region,
        )
        df = sql_engine.query(sql)
        self.res_df = pd.concat([self.res_df, df])

    def typeid_province(self, province_no, province_region):
        sql = """select 
                    concat(type_id, '_', {province_no}) as id, 
                    concat(type_id, '_', {province_no}) as cons_no, 
                    concat(type_code, '_', '全省') as cons_name,
                    {province_region} as region_code,
                    type_id,
                    type_code,
                    pare_type_id,
                    pare_type_code,
                    type_code_sort,
                    99999 as on5,
                    9999999 as on7,
                    '全省' as shi,
                    '全省' as xian,
                    '全省' as org_name,
                    {select_sql}
                 from {table_name} 
                 group by type_id, type_code, pare_type_id, pare_type_code, type_code_sort
              """.format(
            table_name=table_name,
            select_sql=self.select_sql,
            province_no=province_no,
            province_region=province_region,
        )
        df = sql_engine.query(sql)
        self.res_df = pd.concat([self.res_df, df])

    def all_on5(self):
        sql = """select 
                    concat('99999', '_', on5) as id, 
                    concat('99999', '_', on5) as cons_no, 
                    concat('全行业', '_', shi) as cons_name,
                    max(region_code) as region_code,
                    99999 as type_id,
                    '全行业' as type_code,
                    99999 as pare_type_id,
                    '全行业' as pare_type_code,
                    99999 as type_code_sort,
                    on5,
                    9999999 as on7,
                    shi,
                    shi as xian,
                    shi as org_name,
                    {select_sql}
                 from {table_name} 
                 group by on5, shi, shi;
              """.format(
            table_name=table_name, select_sql=self.select_sql
        )
        df = sql_engine.query(sql)
        self.res_df = pd.concat([self.res_df, df])

    def pare_on5(self):
        sql = """select 
                    concat(pare_type_id, '_', on5) as id, 
                    concat(pare_type_id, '_', on5) as cons_no, 
                    concat(pare_type_code, '_', shi) as cons_name,
                    max(region_code) as region_code,
                    pare_type_id as type_id,
                    pare_type_code as type_code,
                    pare_type_id,
                    pare_type_code,
                    type_code_sort,
                    on5,
                    9999999 as on7,
                    shi,
                    shi as xian,
                    shi as org_name,
                    {select_sql}
                 from {table_name} 
                 group by on5, shi, pare_type_id, pare_type_code, type_code_sort;
              """.format(
            table_name=table_name, select_sql=self.select_sql
        )
        df = sql_engine.query(sql)
        self.res_df = pd.concat([self.res_df, df])

    def typeid_on5(self):
        sql = """select 
                    concat(type_id, '_', on5) as id, 
                    concat(type_id, '_', on5) as cons_no, 
                    concat(type_code, '_', shi) as cons_name,
                    max(region_code) as region_code,
                    type_id,
                    type_code,
                    pare_type_id,
                    pare_type_code,
                    type_code_sort,
                    on5,
                    9999999 as on7,
                    shi,
                    shi as xian,
                    shi as org_name,
                    {select_sql}
                 from {table_name} 
                 group by on5, shi, type_id, type_code, pare_type_id, 
                          pare_type_code, type_code_sort;
              """.format(
            table_name=table_name, select_sql=self.select_sql
        )
        df = sql_engine.query(sql)
        self.res_df = pd.concat([self.res_df, df])

    def all_on7(self):
        sql = """select 
                    concat('99999', '_', on7) as id, 
                    concat('99999', '_', on7) as cons_no, 
                    concat('全行业', '_', xian) as cons_name,
                    region_code,
                    99999 as type_id,
                    '全行业' as type_code,
                    99999 as pare_type_id,
                    '全行业' as pare_type_code,
                    99999 as type_code_sort,
                    on5,
                    on7,
                    shi,
                    xian,
                    xian as org_name,
                    {select_sql}
                 from {table_name} 
                 group by on7, xian, on5, shi, region_code;
              """.format(
            table_name=table_name, select_sql=self.select_sql
        )
        df = sql_engine.query(sql)
        self.res_df = pd.concat([self.res_df, df])

    def pare_on7(self):
        sql = """select 
                    concat(pare_type_id, '_', on7) as id, 
                    concat(pare_type_id, '_', on7) as cons_no, 
                    concat(pare_type_code, '_', xian) as cons_name,
                    region_code,
                    pare_type_id as type_id,
                    pare_type_code as type_code,
                    pare_type_id,
                    pare_type_code,
                    type_code_sort,
                    on5,
                    on7,
                    shi,
                    xian,
                    xian as org_name,
                    {select_sql}
                 from {table_name} 
                 group by on7, xian, on5, shi, pare_type_id, pare_type_code, type_code_sort, 
                 region_code;
              """.format(
            table_name=table_name, select_sql=self.select_sql
        )
        df = sql_engine.query(sql)
        self.res_df = pd.concat([self.res_df, df])

    def typid_on7(self):
        sql = """select 
                     concat(type_id, '_', on7) as id, 
                     concat(type_id, '_', on7) as cons_no, 
                     concat(type_code, '_', xian) as cons_name,
                     region_code,
                     type_id,
                     type_code,
                     pare_type_id,
                     pare_type_code,
                     type_code_sort,
                     on5,
                     on7,
                     shi,
                     xian,
                     xian as org_name,
                     {select_sql}
                 from {table_name} 
                 group by on7, xian, on5, shi, type_id, type_code, pare_type_id, pare_type_code, 
                          type_code_sort, region_code
              """.format(
            table_name=table_name, select_sql=self.select_sql
        )
        df = sql_engine.query(sql)
        self.res_df = pd.concat([self.res_df, df])

    def get_province_no(self):
        sql = "select org_no from real_org_no where org_level=0"
        df = sql_engine.query(sql)
        province_no = df["org_no"].tolist()[0]
        return province_no

    def get_province_region(self):
        sql = """
                SELECT distinct region_code from {table_name} where on5 = 
                (select org_no from real_org_no where org_level=1 ORDER BY org_no limit 1) limit 1
              """.format(
            table_name=table_name
        )
        df = sql_engine.query(sql)
        province_region = df["region_code"].tolist()[0]
        return province_region

    def main(self):
        province_no = self.get_province_no()
        province_region = self.get_province_region()
        self.typid_on7()
        self.pare_on7()
        self.all_on7()
        self.typeid_on5()
        self.pare_on5()
        self.all_on5()
        self.typeid_province(province_no, province_region)
        self.pare_province(province_no, province_region)
        self.all_province(province_no, province_region)

        self.res_df["c_id"] = [100000001 + i for i in range(len(self.res_df))]
        self.res_df["cons_num"] = 0
        sql_engine.insert_df(self.res_df, self.table_name, drop_id=False)
        # self.ep.to_es(self.res_df, index_name, request_timeout=60)


if __name__ == "__main__":
    index_name = "aclr_base_doc_all_hebei"
    table_name = "c_cons_all_virtual2"

    vu = AddVirtualUsers(table_name)
    vu.main()
